/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;
import DTO.*;
import DAO.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class CReport {
    SubConfig config;
    public CReport(){
        config = SubConfig.getInstance();
        config.init();
    }
    /**
     * Hàm thực hiện chức năng lấy dữ liệu để xuất ra report
     * @param begin Thời gian bắt đầu
     * @param end Thời gian kết thúc
     * @param kho_id Kho tương ứng
     * @param loai_phieu Loại phiếu tương úng( nhâp kho xuất kho)
     * @return Trả về danh sách các sản phẩm
     */
    public List<CHoaDon> getHoaDon(Timestamp begin,Timestamp end,long kho_id,String loai_phieu){                 
        String mysql ="select * from " + CFields.tb_hoa_don + " where " + CFields.ngay_tao
                + String.format(" between ? and ? ");//,begin,end
        mysql = "SELECT sp.ma_sp,sp.ten_san_pham,"
                + "sum(cthd.so_luong) as so_luong,cthd.don_gia,sum(gia_von) as gia_von,sum(cthd.thanh_tien) as thanh_tien"
                ;
        mysql+= " FROM chi_tiet_hoa_don cthd,hoa_don hd,san_pham sp ";
        mysql += " where cthd.hoa_don_id = hd.id and sp.id = cthd.san_pham_id ";
        mysql += " and hd.ngay_tao between ? and ? ";
        mysql += " and loai_phieu=? and hd.kho_id= ? group by sp.ma_sp,sp.ten_san_pham,cthd.don_gia ";
        System.out.println("mysql : " + mysql);
        Object []values = {begin,end,loai_phieu,kho_id};//"2014-10-01","2014-10-30"
        CHoaDonDAO hoadonDAO = new CHoaDonDAO();
        CChiTietHoaDonDAO cthdDao = new CChiTietHoaDonDAO();
        ResultSet rs = cthdDao.getResultSet(mysql, values, config);
        return hoadonDAO.getAll(mysql,values, config);// values,
    }
    /**
     * 
     * @param begin
     * @param end
     * @param kho_id
     * @param loai_phieu
     * @return
     * @throws SQLException 
     */
     public List<CChiTietHoaDon> getChiTietHoaDon(Timestamp begin,Timestamp end,long kho_id,String loai_phieu) throws SQLException{                 
        String mysql ="";        
        mysql = "select ma_sp,ten_san_pham,sum(so_luong) as so_luong,sum(thanh_tien) as thanh_tien ";
        mysql += " from vChiTietHoaDonBH where ngay_tao between ? and ? and kho_id= ? ";
        mysql += " group by ma_sp,ten_san_pham order by ma_sp ";
        Object []values = {begin,end,kho_id};//"2014-10-01","2014-10-30"//loai_phieu,
        CHoaDonDAO hoadonDAO = new CHoaDonDAO();
        CChiTietHoaDonDAO cthdDao = new CChiTietHoaDonDAO();
        ResultSet rs = cthdDao.getResultSet(mysql, values, config);
        List<CChiTietHoaDon> list = new ArrayList<CChiTietHoaDon>();
        if(rs != null){
            while(rs.next()){    
                list.add(this.getItem(rs));
            }
        }
        rs.close();
        //danh sach nhap hang
        mysql = "select ma_sp,ten_san_pham,sum(so_luong) as so_luong,sum(thanh_tien) as thanh_tien ";
        mysql += " from vChiTietHoaDonXH where ngay_tao between ? and ? and kho_id= ? ";
        mysql += " group by ma_sp,ten_san_pham order by ma_sp ";
        Object []values1 = {begin,end,kho_id};//"2014-10-01","2014-10-30"//loai_phieu,
        rs = cthdDao.getResultSet(mysql, values, config);
        List<CChiTietHoaDon> lnhaphang = new ArrayList<CChiTietHoaDon>();
        if(rs != null){
            while(rs.next()){    
                lnhaphang.add(this.getItem(rs));
            }
        }
        rs.close();
        for(CChiTietHoaDon item : list){
            CChiTietHoaDon current = this.getItem(lnhaphang, item.getMaSP());
            if(current != null){
                item.setGiaVon(current.getThanhTien());
            }
        }        
        return list;
    }
     
     public List<CChiTietHoaDon> getDoanhThuCuaHang(Timestamp begin,Timestamp end,long kho_id) throws SQLException{
         String mysql ="";        
        mysql = "select ma_sp,ten_san_pham,sum(so_luong) as so_luong,sum(thanh_tien) as thanh_tien ";
        mysql += " from vChiTietHoaDonBH where ngay_tao between ? and ? and kho_id= ? ";
        mysql += " group by ma_sp,ten_san_pham order by ma_sp ";
        Object []values = {begin,end,kho_id};//"2014-10-01","2014-10-30"//loai_phieu,
        CHoaDonDAO hoadonDAO = new CHoaDonDAO();
        CChiTietHoaDonDAO cthdDao = new CChiTietHoaDonDAO();
        ResultSet rs = cthdDao.getResultSet(mysql, values, config);
        List<CChiTietHoaDon> list = new ArrayList<CChiTietHoaDon>();
        if(rs != null){
            while(rs.next()){    
                list.add(this.getItem(rs));
            }
        }
        rs.close();
        return list;
     }
     
     private CChiTietHoaDon getItem(List<CChiTietHoaDon> list,String ma_sp){
         for(CChiTietHoaDon item : list){
             if(item.getMaSP().equals(ma_sp)){
                 return item;
             }
         }
         return null;
     }
     
     private CChiTietHoaDon getItem(ResultSet rs) throws SQLException{
         CChiTietHoaDon item = new CChiTietHoaDon();
         item.setMaSP(rs.getString("ma_sp").trim());
         item.setTenSanPham(rs.getString("ten_san_pham"));
         item.setSoLuong(rs.getInt("so_luong"));         
         //item.setDongia(rs.getDouble(CFields.don_gia));
         //item.setGiaVon(rs.getDouble("gia_von"));
         item.setThanhTien(rs.getDouble("thanh_tien"));
         return item;
     }
     
      public List<CTon_Kho> getTonKho(long kho_id,int month,int year){
        CTonKhoDAO ton_khoDAO = new CTonKhoDAO();
        List<CTon_Kho> list = new ArrayList<CTon_Kho>();
        String mysql  = "select *from vton_kho "  + " where " + CFields.kho_id + "=? and thang =? and nam =?" ;
        Object []values = {kho_id,month,year};
        list = ton_khoDAO.getAll(mysql, values, config);
        return list;
      }
      
      public List<CHeThong> getBaoDoanhThuHeThong(Timestamp begin,Timestamp end){
          List<CHeThong> list = new ArrayList<CHeThong>();
          CHeThongDAO htDAO = new CHeThongDAO();
          String mysql =" select kho_id,ten_kho,sum(thanh_tien) as thanh_tien from vHDBH where ngay_tao between ? and ?";
          mysql = mysql + " group by kho_id,ten_kho ";
          Object []values = {begin,end};
          list = htDAO.getAll(mysql, values, config);
          return list;
      }
      
      public List<CHeThong>getLoiNhuanHeThong(Timestamp begin,Timestamp end){
          List<CHeThong> list = new ArrayList<CHeThong>();
          CHeThongDAO htDAO = new CHeThongDAO();
          String mysql =" select kho_id,ten_kho,sum(thanh_tien) as thanh_tien from vHDBH where ngay_tao between ? and ?";
          mysql = mysql + " group by kho_id,ten_kho ";
          Object []values = {begin,end};
          list = htDAO.getAll(mysql, values, config);
          
          List<CHeThong> lnhap = new ArrayList<CHeThong>();
          mysql =" select kho_id,ten_kho,sum(thanh_tien) as thanh_tien from vNhapHang where ngay_tao between ? and ?";
          mysql = mysql + " group by kho_id,ten_kho ";
          lnhap = htDAO.getAll(mysql, values, config);
          for(CHeThong item : list){
              CHeThong find = htDAO.getItem(lnhap, item.getId());
              if(find != null){
                  double loinhuan = item.getThanhtien() - find.getThanhtien();
                  item.setThanhtien(loinhuan);
              }
          }     
          return list;
      }
      
      
}
